﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmPayroll

    Private Sub btnGenerate_Click(sender As System.Object, e As System.EventArgs) Handles btnGenerate.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.GridPayroll.Rows.Clear()

            'Check if payroll already generated
            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Payroll " & _
                                      "Where Year=" & Me.DTPPayroll.Value.Year & " and Month=" & Me.DTPPayroll.Value.Month, cnn)
            Dim X As Integer

            cnn.Open()
            X = CInt(cmd.ExecuteScalar)
            cnn.Close()


            If X = 0 Then
                FillNewPayroll()
            Else
                FillOldPayroll()
            End If

            'Change Colours
            SetColours()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub SetColours()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each row As DataGridViewRow In Me.GridPayroll.Rows
                row.Cells(Me.DGColGrossSalary.Index).Style.BackColor = Color.LightGreen
                row.Cells(Me.DGColTotDeduct.Index).Style.BackColor = Color.LightGreen
                row.Cells(Me.DGColNetSalary.Index).Style.BackColor = Color.LightSkyBlue
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Sub FillNewPayroll()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From StaffProfiles Where EndOfServiceDate Is Null", cnn)
            Dim Reader As SqlDataReader
            Dim SocialInsurance As Double

            Me.GridPayroll.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                If Reader.Item("Nationality") = "Saudi" Then
                    SocialInsurance = CDbl((CDbl(Reader.Item("SalaryBS")) + CDbl(Reader.Item("SalaryHA"))) * 9 / 100).ToString("N2")
                Else
                    SocialInsurance = 0
                End If

                Me.GridPayroll.Rows.Add({Reader.Item("Branch"), Reader.Item("EmpNo"), Reader.Item("FullNameEn"), Reader.Item("FullNameAr"), Reader.Item("Nationality"), _
                                         CDbl(Reader.Item("SalaryBS")).ToString("N2"), CDbl(Reader.Item("SalaryHA")).ToString("N2"), _
                                         CDbl(Reader.Item("SalaryTA")).ToString("N2"), CDbl(Reader.Item("SalaryFuel")).ToString("N2"), _
                                         CDbl(Reader.Item("SalarySupervision")).ToString("N2"), CDbl(Reader.Item("SalaryTuitionFees")).ToString("N2"), _
                                         CDbl(Reader.Item("SalaryMobile")).ToString("N2"), CDbl(Reader.Item("Others")).ToString("N2"), _
                                         CDbl(Reader.Item("TotalSalary")).ToString("N2"), SocialInsurance.ToString("N2"), "0.00", "0.00", "0.00", "0.00"})
            End While
            cnn.Close()

            Calculate()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillOldPayroll()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Payroll " & _
                                      "Where Year=" & Me.DTPPayroll.Value.Year & " and Month=" & Me.DTPPayroll.Value.Month, cnn)
            Dim Reader As SqlDataReader

            Me.GridPayroll.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridPayroll.Rows.Add({Reader.Item("Branch"), Reader.Item("EmpNo"), Reader.Item("FullNameEn"), Reader.Item("FullNameAr"), Reader.Item("Nationality"), _
                                         CDbl(Reader.Item("SalaryBS")).ToString("N2"), CDbl(Reader.Item("SalaryHA")).ToString("N2"), _
                                         CDbl(Reader.Item("SalaryTA")).ToString("N2"), CDbl(Reader.Item("SalaryFuel")).ToString("N2"), _
                                         CDbl(Reader.Item("SalarySupervision")).ToString("N2"), CDbl(Reader.Item("SalaryTuitionFees")).ToString("N2"), _
                                         CDbl(Reader.Item("SalaryMobile")).ToString("N2"), CDbl(Reader.Item("SalaryOthers")).ToString("N2"), _
                                         CDbl(Reader.Item("GrossSalary")).ToString("N2"), _
                                         CDbl(Reader.Item("SocialInsurance")).ToString("N2"), CDbl(Reader.Item("Absence")).ToString("N2"), _
                                         CDbl(Reader.Item("Delay")).ToString("N2"), CDbl(Reader.Item("Penalties")).ToString("N2"), _
                                         CDbl(Reader.Item("Loans")).ToString("N2"), CDbl(Reader.Item("NetSalary")).ToString("N2")})
            End While
            cnn.Close()

            Calculate()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmPayroll_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
    End Sub

    Private Sub GridPayroll_CellEndEdit(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridPayroll.CellEndEdit
        Try
            Me.GridPayroll.CurrentCell.Value = CDbl(Me.GridPayroll.CurrentCell.Value).ToString("N2")

            Calculate()
        Catch ex As Exception
            Me.GridPayroll.CurrentCell.Value = "0.00"
        End Try
    End Sub

    Sub Calculate()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each row As DataGridViewRow In Me.GridPayroll.Rows
                'Total Deduction
                row.Cells(Me.DGColTotDeduct.Index).Value = CDbl(CDbl(row.Cells(Me.DGColSocialInsurance.Index).Value) + _
                                                                CDbl(row.Cells(Me.DGColAbsence.Index).Value) + _
                                                                CDbl(row.Cells(Me.DGColDelay.Index).Value) + _
                                                                CDbl(row.Cells(Me.DGColPenalties.Index).Value) + _
                                                                CDbl(row.Cells(Me.DGColLoans.Index).Value)).ToString("N2")

                'Net Salary
                row.Cells(Me.DGColNetSalary.Index).Value = CDbl(CDbl(row.Cells(Me.DGColGrossSalary.Index).Value) - _
                                                                CDbl(row.Cells(Me.DGColSocialInsurance.Index).Value) - _
                                                                CDbl(row.Cells(Me.DGColAbsence.Index).Value) - _
                                                                CDbl(row.Cells(Me.DGColDelay.Index).Value) - _
                                                                CDbl(row.Cells(Me.DGColPenalties.Index).Value) - _
                                                                CDbl(row.Cells(Me.DGColLoans.Index).Value)).ToString("N2")
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        If Me.GridPayroll.Rows.Count = 0 Then
            MsgBox("Please generate the payroll first")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans
                cmd.Connection = cnn

                '1) Delete old payroll
                cmd.CommandText = "Delete From Payroll Where Year=" & Me.DTPPayroll.Value.Year & " and Month=" & Me.DTPPayroll.Value.Month
                cmd.ExecuteNonQuery()

                For Each row As DataGridViewRow In Me.GridPayroll.Rows
                    '2) Save new payroll
                    cmd.CommandText = "Insert Into Payroll " & _
                                      "(Branch,Year,Month,EmpNo,FullNameEn,FullNameAr,Nationality,SalaryBS,SalaryHA,SalaryTA,SalaryFuel,SalarySupervision," & _
                                      "SalaryTuitionFees,SalaryMobile,SalaryOthers,GrossSalary,SocialInsurance,Absence,Delay,Penalties,Loans,NetSalary) " & _
                                      "Values " & _
                                      "(@Branch,@Year,@Month,@EmpNo,@FullNameEn,@FullNameAr,@Nationality,@SalaryBS,@SalaryHA,@SalaryTA,@SalaryFuel,@SalarySupervision," & _
                                      "@SalaryTuitionFees,@SalaryMobile,@SalaryOthers,@GrossSalary,@SocialInsurance,@Absence,@Delay,@Penalties,@Loans,@NetSalary)"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@Branch", row.Cells(Me.DGColBranch.Index).Value)
                    cmd.Parameters.AddWithValue("@Year", Me.DTPPayroll.Value.Year)
                    cmd.Parameters.AddWithValue("@Month", Me.DTPPayroll.Value.Month)

                    cmd.Parameters.AddWithValue("@EmpNo", row.Cells(Me.DGColEmpID.Index).Value)
                    cmd.Parameters.AddWithValue("@FullNameEn", row.Cells(Me.DGColName.Index).Value)
                    cmd.Parameters.AddWithValue("@FullNameAr", row.Cells(Me.DGColNameAr.Index).Value)
                    cmd.Parameters.AddWithValue("@Nationality", row.Cells(Me.DGColNationality.Index).Value)

                    cmd.Parameters.AddWithValue("@SalaryBS", CDbl(row.Cells(Me.DGColBS.Index).Value))
                    cmd.Parameters.AddWithValue("@SalaryHA", CDbl(row.Cells(Me.DGColHA.Index).Value))
                    cmd.Parameters.AddWithValue("@SalaryTA", CDbl(row.Cells(Me.DGColTA.Index).Value))
                    cmd.Parameters.AddWithValue("@SalaryFuel", CDbl(row.Cells(Me.DGColFuelA.Index).Value))
                    cmd.Parameters.AddWithValue("@SalarySupervision", CDbl(row.Cells(Me.DGColSupervision.Index).Value))
                    cmd.Parameters.AddWithValue("@SalaryTuitionFees", CDbl(row.Cells(Me.DGColTuitionA.Index).Value))
                    cmd.Parameters.AddWithValue("@SalaryMobile", CDbl(row.Cells(Me.DGColMobileA.Index).Value))
                    cmd.Parameters.AddWithValue("@SalaryOthers", CDbl(row.Cells(Me.DGColOtherA.Index).Value))
                    cmd.Parameters.AddWithValue("@GrossSalary", CDbl(row.Cells(Me.DGColGrossSalary.Index).Value))

                    cmd.Parameters.AddWithValue("@SocialInsurance", CDbl(row.Cells(Me.DGColSocialInsurance.Index).Value))
                    cmd.Parameters.AddWithValue("@Absence", CDbl(row.Cells(Me.DGColAbsence.Index).Value))
                    cmd.Parameters.AddWithValue("@Delay", CDbl(row.Cells(Me.DGColDelay.Index).Value))
                    cmd.Parameters.AddWithValue("@Penalties", CDbl(row.Cells(Me.DGColPenalties.Index).Value))
                    cmd.Parameters.AddWithValue("@Loans", CDbl(row.Cells(Me.DGColLoans.Index).Value))
                    cmd.Parameters.AddWithValue("@NetSalary", CDbl(row.Cells(Me.DGColNetSalary.Index).Value))
                    cmd.ExecuteNonQuery()
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnPrintPayrollAdmin_Click(sender As System.Object, e As System.EventArgs) Handles btnPrintPayrollAdmin.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From Payroll Where Year=" & Me.DTPPayroll.Value.Year & " and Month=" & Me.DTPPayroll.Value.Month, cnn)
            Dim dap1 As New SqlDataAdapter("Select * From StaffProfiles Where Admin=1", cnn)
            Dim das As New DsPayroll

            dap.Fill(das, "Payroll")
            dap1.Fill(das, "StaffProfiles")

            Dim rpt As New Payroll

            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'Category PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Category")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = "Admin"
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Month PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Month")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPayroll.Value.ToString("MMMM, yyyy")
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.ShowDialog()

            rpt.Dispose()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnPrintPaySlipsStaff_Click(sender As System.Object, e As System.EventArgs) Handles btnPrintPaySlipsStaff.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From Payroll Where Year=" & Me.DTPPayroll.Value.Year & " and Month=" & Me.DTPPayroll.Value.Month, cnn)
            Dim dap1 As New SqlDataAdapter("Select * From StaffProfiles Where Admin=0", cnn)
            Dim das As New DsPayroll

            dap.Fill(das, "Payroll")
            dap1.Fill(das, "StaffProfiles")

            Dim rpt As New PaySlips

            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'Month PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Month")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPayroll.Value.ToString("MMMM, yyyy")
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.ShowDialog()

            rpt.Dispose()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnPrintSocialInsurance_Click(sender As System.Object, e As System.EventArgs) Handles btnPrintSocialInsurance.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From Payroll Where Year=" & Me.DTPPayroll.Value.Year & " and Month=" & Me.DTPPayroll.Value.Month & _
                                          " and Nationality=N'Saudi'", cnn)
            Dim das As New DataSet

            dap.Fill(das, "Payroll")

            Dim rpt As New SocialInsurance

            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'Month PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Month")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPayroll.Value.ToString("MMMM, yyyy")
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.ShowDialog()

            rpt.Dispose()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class